package com.sit.estatemanagementserver.dao;

import com.sit.estatemanagementserver.enity.Owner;
import com.sit.estatemanagementserver.enity.Register;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class OwnerDao {
    @Autowired
    DatabaseManager db;

    // 重构: 适配新的 Owner(phone, password, username) 构造函数
    private Owner resultSetToOwner(ResultSet rs) throws SQLException {
        return new Owner(
                rs.getString(DatabaseContract.OwnerEntry.COLUMN_PHONE),
                rs.getString(DatabaseContract.OwnerEntry.COLUMN_PASSWORD),
                rs.getString(DatabaseContract.OwnerEntry.COLUMN_USERNAME)
        );
    }

    public boolean register(Register register){
        // 重构: 更新SQL语句以匹配新的列名 (name -> username)
        String addOwnerSql = "INSERT INTO " + DatabaseContract.OwnerEntry.TABLE_NAME + " (" +
                DatabaseContract.OwnerEntry.COLUMN_PHONE + ", " +
                DatabaseContract.OwnerEntry.COLUMN_PASSWORD + ", " +
                DatabaseContract.OwnerEntry.COLUMN_USERNAME + ") VALUES (?, ?, ?)";
        String addRoomSql = "INSERT INTO " + DatabaseContract.RoomEntry.TABLE_NAME + " (" +
                DatabaseContract.RoomEntry.COLUMN_ROOM_NUMBER + ", " +
                DatabaseContract.RoomEntry.COLUMN_OWNER_PHONE_FK + ") VALUES (?, ?)";

        Connection conn = null;
        try {
            conn = db.connect();
            conn.setAutoCommit(false);

            try (PreparedStatement pstmtOwner = conn.prepareStatement(addOwnerSql)) {
                pstmtOwner.setString(1, register.getPhone());
                pstmtOwner.setString(2, register.getPassword());
                pstmtOwner.setString(3, register.getName()); // Register DTO中是name字段
                pstmtOwner.executeUpdate();
            }

            try (PreparedStatement pstmtRoom = conn.prepareStatement(addRoomSql)) {
                pstmtRoom.setString(1, register.getRoomNumber());
                pstmtRoom.setString(2, register.getPhone());
                pstmtRoom.executeUpdate();
            }

            conn.commit();
            return true;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error during registration transaction: " + e.getMessage());
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    System.err.println(DatabaseManager.TAG + ": Error during transaction rollback: " + ex.getMessage());
                }
            }
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                } catch (SQLException e) {
                    System.err.println(DatabaseManager.TAG + ": Error closing connection: " + e.getMessage());
                }
            }
        }
    }

    public boolean addOwner(Owner owner) {
        String sql = "INSERT INTO " + DatabaseContract.OwnerEntry.TABLE_NAME + " (" +
                DatabaseContract.OwnerEntry.COLUMN_PHONE + ", " +
                DatabaseContract.OwnerEntry.COLUMN_PASSWORD + ", " +
                DatabaseContract.OwnerEntry.COLUMN_USERNAME + ") VALUES (?, ?, ?)";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, owner.getPhone());
            pstmt.setString(2, owner.getPassword());
            pstmt.setString(3, owner.getUsername()); // 重构: 使用getUsername()
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error adding owner '" + owner.getPhone() + "': " + e.getMessage());
            return false;
        }
    }

    public Owner getOwner(String phone) {
        String sql = "SELECT * FROM " + DatabaseContract.OwnerEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.OwnerEntry.COLUMN_PHONE + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, phone);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return resultSetToOwner(rs);
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting owner '" + phone + "': " + e.getMessage());
        }
        return null;
    }

    public List<Owner> getAllOwners() {
        List<Owner> owners = new ArrayList<>();
        String sql = "SELECT * FROM " + DatabaseContract.OwnerEntry.TABLE_NAME;
        try (Connection conn = db.connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                owners.add(resultSetToOwner(rs));
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting all owners: " + e.getMessage());
        }
        return owners;
    }

    public boolean updateOwner(Owner owner) {
        String sql = "UPDATE " + DatabaseContract.OwnerEntry.TABLE_NAME + " SET " +
                DatabaseContract.OwnerEntry.COLUMN_PASSWORD + " = ?, " +
                DatabaseContract.OwnerEntry.COLUMN_USERNAME + " = ? WHERE " +
                DatabaseContract.OwnerEntry.COLUMN_PHONE + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, owner.getPassword());
            pstmt.setString(2, owner.getUsername()); // 重构: 使用getUsername()
            pstmt.setString(3, owner.getPhone());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error updating owner '" + owner.getPhone() + "': " + e.getMessage());
            return false;
        }
    }

    public boolean deleteOwner(String phone) {
        String sql = "DELETE FROM " + DatabaseContract.OwnerEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.OwnerEntry.COLUMN_PHONE + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, phone);
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error deleting owner '" + phone + "': " + e.getMessage());
            return false;
        }
    }
}